<?php

namespace Application\Model\Dao;

use Zend\Db\Adapter\Driver\ResultInterface;
use Zend\Db\ResultSet\ResultSet;
use Zend\Config\Reader\Xml;
use Zend\Db\Sql\Platform\AbstractPlatform;
use Zend\Filter\Boolean;
use Zend\Json\Json;

/**
 * Class UpdateDao
 * @package Application\Model\Dao
 *
 *
 */
class UpdateDao extends BaseDao
{

    /**
     * @var array
     * insert into user_update_type(id, type) values(1, 'UPDATE');
     * insert into user_update_type(id, type) values(2, 'INFO');
     * insert into user_update_type(id, type) values(3, 'NOTICE');
     * insert into user_update_type(id, type) values(4, 'MESSAGE');
     * insert into user_update_type(id, type) values(5, 'DISCUSSION');
     */
    public static $UPDATE_TYPE = array('UPDATE' => 1,
        'INFO' => 2,
        'NOTICE' => 3,
        'MESSAGE' => 4,
        'DISCUSSION' => 5
    );

    public static $UPDATE_TARGET_TYPE = array('GROUP' => 1,
        'USER' => 2,
    );

    /**
     * @var string
     */
    public static $INSERT_UPDATE = "insert into user_update(update_txt, updater, updatedAt, photoset, extraData, poll_data, type, update_version)
    values (?, ?, now(), ?, ?,?, ?, 0)";

    public static $INSERT_UPDATE_TARGET = "insert into user_update_target(type, target_id, user_update) values (?, ?, ?)";

    public static $DELETE_UPDATE = "delete from user_update where id = ?";

    public static $SAVE_UPDATE_TXT = "update user_update set update_txt = ? where id = ?";


    public static $SAVE_UPDATE_POLL_with_update_version = "update user_update  set poll_data = ?, update_version = ? where id = ? and update_version = ? ";


    public static $SAVE_UPDATE_POLL = "update user_update set poll_data = ? where id = ? ";

    public static $SAVE_POLL_RESPONSE = "insert into agenda_response(agenda_option, responder, comment, update_id) value(?,?,?, ?) ";

    public static $EDIT_POLL_RESPONSE = "update agenda_response set agenda_option = ? where id =?";

    public static $GET_POLL_DATA = "select poll_data, update_version from user_update where id = ?";


    public static $UPDATE_POLL_RESPONSE = "update agenda_response set comment = ? where id = ?";

    public static $DELETE_POLL_RESPONSE = "delete from agenda_response where id = ?";

    public static $GET_ALL_POLL_RESPONSE = "select agenda_response.id as arid, comment, user.id as id,
                    agenda_response.agenda_option as agenda_option,
                    user.name as name, user.photoset as photoset, user_update.poll_data as poll_data from agenda_response
                     left join user on (user.id = agenda_response.responder) left join user_update on(agenda_response.update_id = user_update.id) where
                     update_id = ?";


    public static $GET_POLL_RESPONSE = "select agenda_response.id as arid, comment, user.id as id,
                    agenda_response.agenda_option as agenda_option,
                    user.name as name, user.photoset as photoset from agenda_response
                     left join user on (user.id = agenda_response.responder) where
                     update_id = ? and responder = ?";


    public static $DELETE_UPDATE_POLL = "update user_update set poll_data = null where id = ?";


    public static $DELETE_UPDATE_TARGET = "delete from user_update_target where user_update = ?";


    public static $DELETE_COMMENT = "delete from user_comment where id = ?";


    public static $INSERT_COMMENT = "insert into user_comment(comment, commenter, parentId, parentType, commentAt)
      values (?, ?, ? , ?, now())";



    public static $WHERE_CODITION_AFTER = 'user_update_target.user_update > ?';

    public static $WHERE_CODITION_BEFORE = 'user_update_target.user_update < ?';


    /**
     * @var string
     * Explaining the query.
     *
     *
     *
     *
    select user_update_adapter.upid as upid,user_update_adapter.title as title, user_update_adapter.update_txt as update_txt, user_update_adapter.date as date,
    user_update_adapter.uid as uid, user_update_adapter.photoset as photoset,
    user_update_adapter.name as name, user_update_adapter.fileUpload as fileUpload,
    user_update_adapter.update_type, user_update_adapter.user_update_target_type,user_update_adapter.poll_data as poll_data,
    user_comment.commentAt as
    comment_date,user_comment.id as c_id, user_comment.comment as comment,
    commentor.name as commentor, commentor.photoset as c_photoset, commentor.id as comment_user_id,
    response.responder as responder, response.id as response_id, response.agenda_option as response,
    response.comment as response_comment from
    (
    select user_update.id as upid, user_update.update_txt as update_txt, user_update.updatedAt as date,
    user_update.photoset as fileUpload, user_update.extraData as title, user_update.type as update_type,
    user_update.poll_data as poll_data,  updator.id as uid, updator.photoset as photoset,
    updator.name as name, interested_update.user_update_target_type as user_update_target_type  from
    (
    select user_update_target.user_update  as user_update_id, user_update_target.type as user_update_target_type  from group_user inner join user on user.id = group_user.user_id
    inner join user_update_target on (user_update_target.target_id = group_user.group_id and user_update_target.type='group')
    where user.id = 2 and user_update_target.user_update > 0
    union
    select user_update_target.user_update as user_update_id, user_update_target.type as user_update_target_type  from user_update_target inner join user on
    (user_update_target.target_id = user.id and user_update_target.type='user') where
    user.id = 2 and user_update_target.user_update > 0
    ) as interested_update

    inner join user_update on (interested_update.user_update_id = user_update.id) inner join user as updator
    on (user_update.updater = updator.id) where user_update.type in (1,2) order by  user_update.id desc limit 10
    ) as user_update_adapter
    left join user_comment on (user_comment.parentId = user_update_adapter.upid) left join
    user as commentor on (commentor.id = user_comment.commenter)  left join (select * from agenda_response where responder = 2)

    as response on(response.update_id = user_update_adapter.upid)

    order by  user_update_adapter.upid desc, user_comment.id  desc;
     *
     */
    public static $GET_UPDATE_JOIN_AND_CONDITIONS = "
    select user_update_adapter.upid as upid,user_update_adapter.title as title, user_update_adapter.update_txt as update_txt, user_update_adapter.date as date,
    user_update_adapter.uid as uid, user_update_adapter.photoset as photoset,
    user_update_adapter.name as name, user_update_adapter.fileUpload as fileUpload,
    user_update_adapter.update_type, user_update_adapter.user_update_target_type,user_update_adapter.poll_data as poll_data,
    user_comment.commentAt as
    comment_date,user_comment.id as c_id, user_comment.comment as comment,
     commentor.name as commentor, commentor.photoset as c_photoset, commentor.id as comment_user_id,
     response.responder as responder, response.id as response_id, response.agenda_option as response,
      response.comment as response_comment from
    (
    select user_update.id as upid, user_update.update_txt as update_txt, user_update.updatedAt as date,
    user_update.photoset as fileUpload, user_update.extraData as title, user_update.type as update_type,
    user_update.poll_data as poll_data,  updator.id as uid, updator.photoset as photoset,
    updator.name as name, interested_update.user_update_target_type as user_update_target_type  from
    (
    select user_update_target.user_update  as user_update_id, user_update_target.type as user_update_target_type  from group_user inner join user on user.id = group_user.user_id
    inner join user_update_target on (user_update_target.target_id = group_user.group_id and user_update_target.type='group')
    where user.id = ? and user.blocked is null and USER_UPDATE_TARGET_CONDITION
    union
    select user_update_target.user_update as user_update_id, user_update_target.type as user_update_target_type  from user_update_target inner join user on
    (user_update_target.target_id = user.id and user_update_target.type='user') where
     user.id = ? and user.blocked is null and USER_UPDATE_TARGET_CONDITION
    ) as interested_update

    inner join user_update on (interested_update.user_update_id = user_update.id) inner join user as updator
    on (user_update.updater = updator.id) where updator.blocked is null and user_update.type in IN_UPDATE_TYPE USER_UPDATE_CONDITION order by  user_update.id desc limit ?
    ) as user_update_adapter
        left join user_comment on (user_comment.parentId = user_update_adapter.upid) left join
    user as commentor on (commentor.id = user_comment.commenter) left join (select * from agenda_response where responder = ?)

     as response on(response.update_id = user_update_adapter.upid)

      where  commentor.blocked is null

    order by  user_update_adapter.upid desc, user_comment.id  desc
    ";




    public static $GET_COMMENTS = "select uc.id as id, u.id as uid, u.photoset c_photoset, uc.comment as comment, uc.commentAt as date,
     u.name as commentor from user_comment  uc inner join user u on (uc.commenter = u.id)  where uc.id > ? and uc.parentId = ?
     order by uc.id desc limit ?";




    public static $GET_UPDATE = "select user_update.id as upid,user_update.extraData as title, user_update.update_txt as update_txt,
    user_update.updatedAt as date,
    user_update.updater as uid, user.photoset as photoset,
    user.name as name, user_update.photoset as fileUpload,
    user_comment.commentAt as
    comment_date,user_comment.id as c_id, user_comment.comment as comment,
    commentor.name as commentor, commentor.photoset as c_photoset, commentor.id as comment_user_id from
    user_update left join user on (user_update.updater = user.id) left join
    user_comment on (user_comment.parentId = user_update.id)
    left join user as commentor on (commentor.id = user_comment.commenter)
    where user_update.id = ? order by user_comment.id desc";



    /**
     * @param $update
     * @param $updator
     * @param $photoSet
     * @param $extraData
     * @param $type
     * @param $target_type
     * @param $target_id
     */
    public function saveUpdateData($update, $updator, $photoSet, $extraData, $type, $target_type, $pollData, $target_id)
    {
        $connection = $this->adapter->driver->getConnection();
        $connection->beginTransaction();

        try {
            $stmt = $this->adapter->createStatement(UpdateDao::$INSERT_UPDATE, array(
                $update,
                $updator,
                $photoSet,
                $extraData,
                $pollData,
                $type
            ));
            $stmt = $stmt->prepare();
            $stmt->execute();
            $stmt = null;
            $id = $connection->getLastGeneratedValue();
            $stmt = $this->adapter->createStatement(UpdateDao::$INSERT_UPDATE_TARGET, array($target_type, $target_id, $id));
            $stmt = $stmt->prepare();
            $stmt->execute();
            $stmt = null;
            $connection->commit();
        } catch (\Exception $e) {
            echo "exception".$e->getMessage();
            $connection->rollback();
        }
    }


    public function saveUpdateTxt($txt, $id){
        $connection = $this->adapter->driver->getConnection();
        $connection->beginTransaction();

        try {
            $stmt = $this->adapter->createStatement(UpdateDao::$SAVE_UPDATE_TXT, array(
                $txt,
                $id,
            ));
            $stmt = $stmt->prepare();
            $stmt->execute();
            $stmt = null;
            $connection->commit();
        } catch (\Exception $e) {
            echo "exception".$e->getMessage();
            $connection->rollback();
        }
    }

    public function savePollCommment($txt, $id){
        $connection = $this->adapter->driver->getConnection();
        $connection->beginTransaction();

        try {
            $stmt = $this->adapter->createStatement(UpdateDao::$UPDATE_POLL_RESPONSE, array(
                $txt,
                $id,
            ));
            $stmt = $stmt->prepare();
            $stmt->execute();
            $stmt = null;
            $connection->commit();
        } catch (\Exception $e) {
            echo "exception";
            $connection->rollback();
        }
    }

    //

    public function savePollData($txt, $id){
        $connection = $this->adapter->driver->getConnection();
        $connection->beginTransaction();

        try {
            $stmt = $this->adapter->createStatement(UpdateDao::$SAVE_UPDATE_POLL, array(
                $txt,
                $id,
            ));
            $stmt = $stmt->prepare();
            $stmt->execute();
            $stmt = null;
            $connection->commit();
        } catch (\Exception $e) {
            echo "exception";
            $connection->rollback();
        }
    }


    public function deletePollData($id){
        $connection = $this->adapter->driver->getConnection();
        $connection->beginTransaction();

        try {
            $stmt = $this->adapter->createStatement(UpdateDao::$DELETE_UPDATE_POLL, array(
                $id,
            ));
            $stmt = $stmt->prepare();
            $stmt->execute();
            $stmt = null;
            $connection->commit();
        } catch (\Exception $e) {
            echo "exception";
            $connection->rollback();
        }
    }

    public function deletePollResponse($response_number, $responder, $responseId, $updateId){

        $connection = $this->adapter->driver->getConnection();
        $connection->beginTransaction();
        try {

            // already responded or not?
            $stmt = $this->adapter->createStatement(UpdateDao::$GET_POLL_RESPONSE, array($updateId, $responder
            ));

            $stmt->prepare();
            $result = $stmt->execute();

            if ($result instanceof ResultInterface && $result->isQueryResult()) {

                if($result->count() > 0){
                    $result = null;
                    $stmt = null;
                    $connection->commit();
                    return;
                }
            }


            $stmt = $this->adapter->createStatement(UpdateDao::$GET_POLL_DATA, array($updateId
            ));

            $stmt->prepare();
            $result = $stmt->execute();
            $poll_data = null;
            $update_version = -1;
            // get poll data json and increase count.
            if ($result instanceof ResultInterface && $result->isQueryResult()) {
                $resultSet = new ResultSet ();
                $resultSet->current($result);
                $result = $resultSet;

                $poll_data = $result->poll_data;

                $update_version =  $resultSet->update_version;
                $poll_data = Json::decode($poll_data);
                $option = $poll_data[$response_number];
                $option['resp_count'] =  $option['resp_count'] - 1;
                $poll_data[$response_number] = $option;
                $poll_data = Json::decode($poll_data);
            }

            // save poll data increased count.

            $stmt = null;
            if($poll_data != null){
                $stmt = $this->adapter->createStatement(UpdateDao::$SAVE_UPDATE_POLL_with_update_version, array(
                    $poll_data,
                    $update_version + 1,
                    $updateId,
                    $update_version,
                ));
                $stmt = $stmt->prepare();
                $stmt->execute();
            }
            $stmt = null;
            // save actual response

            $stmt = $this->adapter->createStatement(UpdateDao::$DELETE_POLL_RESPONSE, array(
                $responseId
            ));
            $stmt = $stmt->prepare();
            $stmt->execute();
            $stmt = null;
            $connection->commit();
        } catch (\Exception $e) {
            echo "exception".$e->getMessage();
            $connection->rollback();
        }
    }


    public function savePollResponse($response_number, $responder, $responseComment, $updateId){

        $connection = $this->adapter->driver->getConnection();
        $connection->beginTransaction();

        try {

            // already responded or not?
            $stmt = $this->adapter->createStatement(UpdateDao::$GET_POLL_RESPONSE, array($updateId, $responder
            ));

            $stmt->prepare();
            $result = $stmt->execute();
            $edit = -1;
            $existingResponse = null;
            if ($result instanceof ResultInterface && $result->isQueryResult()) {
                if($result->count() > 0){

                    $resultSet = new ResultSet ();
                    $resultSet->initialize($result);
                    $result = $resultSet->current();


                    if($result -> agenda_option == $response_number){
                        //agenda_option
                        $result = null;
                        $stmt = null;
                        $connection->commit();
                        return;
                    } else {
                        $edit = $result -> agenda_option;
                        $existingResponse = $result->arid;
                    }
                }
            }


            $stmt = $this->adapter->createStatement(UpdateDao::$GET_POLL_DATA, array($updateId
            ));

            $stmt->prepare();
            $result = $stmt->execute();
            $poll_data = null;
            $update_version = -1;

            // get poll data json and increase count.
            if ($result instanceof ResultInterface && $result->isQueryResult()) {

                $resultSet = new ResultSet ();

                $resultSet->initialize($result);
                $result = $resultSet->current();


                $poll_data = $result->poll_data;

                $update_version =  $result->update_version;

                $poll_data = Json::decode($poll_data, JSON::TYPE_ARRAY);

                $option = $poll_data['options'][$response_number];

                $option['resp_count'] =  $option['resp_count'] + 1;
                $poll_data['options'][$response_number] = $option;

                if($edit > -1){
                    $option = $poll_data['options'][$edit];
                    $option['resp_count'] =  $option['resp_count'] - 1;
                    $poll_data['options'][$edit] = $option;
                }
                $poll_data = Json::encode($poll_data, JSON::TYPE_ARRAY);
            }

            // save poll data increased count.

            $stmt = null;
            if($poll_data != null){
                $stmt = $this->adapter->createStatement(UpdateDao::$SAVE_UPDATE_POLL_with_update_version, array(
                    $poll_data,
                    $update_version + 1,
                    $updateId,
                    $update_version,
                ));
                $stmt = $stmt->prepare();
                $stmt->execute();
            }
            $stmt = null;
            // save actual response

            if($edit == -1){
                $stmt = $this->adapter->createStatement(UpdateDao::$SAVE_POLL_RESPONSE, array(
                    $response_number,
                    $responder,
                    $responseComment,
                    $updateId
                ));
                $stmt = $stmt->prepare();
                $stmt->execute();
                $stmt = null;
                $responseID = $connection->getLastGeneratedValue();
                $connection->commit();
                return $responseID;
            } else {
                $stmt = $this->adapter->createStatement(UpdateDao::$EDIT_POLL_RESPONSE, array(
                    $response_number,
                    $existingResponse
                ));
                $stmt = $stmt->prepare();
                $stmt->execute();
                $stmt = null;
                $connection->commit();
                return $existingResponse;
            }

        } catch (\Exception $e) {
            echo "exception".$e->getMessage();
            $connection->rollback();
        }
    }



    public function deleteUpdate($id)
    {

        $stmt = $this->adapter->createStatement(UpdateDao::$DELETE_UPDATE_TARGET, array(
            $id,
        ));
        $stmt = $stmt->prepare();
        $stmt->execute();
        $stmt = null;


        $stmt = $this->adapter->createStatement(UpdateDao::$DELETE_UPDATE, array(
            $id,
        ));
        $stmt = $stmt->prepare();
        $stmt->execute();
        $stmt = null;
    }

    public function deleteComment($id)
    {
        $stmt = $this->adapter->createStatement(UpdateDao::$DELETE_COMMENT, array(
            $id
        ));
        $stmt = $stmt->prepare();
        $stmt->execute();
        $stmt = null;
    }

    public function getUpdateAgendaResponse($updateId)
    {

        $stmt = $this->adapter->createStatement(UpdateDao::$GET_ALL_POLL_RESPONSE, array($updateId
        ));

        $stmt->prepare();
        $result = $stmt->execute();
        if ($result instanceof ResultInterface && $result->isQueryResult()) {
            $resultSet = new ResultSet ();
            $resultSet->initialize($result);
            $resultArray = array();
            foreach ($resultSet as $result) {
                $this->processUser($result);
                $this->processUpdate($result);
                array_push($resultArray, $result);
            }
            return $resultArray;
        }
    }


    public function getUpdateData($id, $user_id, $updateType, $limit, $after)
    {

        return $this->getUpdateDataWithWhereConditionGiven($id, $user_id, $updateType, $limit, $after, '');

    }

    private function getUpdateDataWithWhereConditionGiven($id, $user_id, $updateType, $limit, $after, $whereConditionOnUpdate){
        $whereCondition = $after?UpdateDao::$WHERE_CODITION_AFTER:UpdateDao::$WHERE_CODITION_BEFORE;

        UpdateDao::$GET_UPDATE_JOIN_AND_CONDITIONS =
            str_replace('USER_UPDATE_TARGET_CONDITION', $whereCondition, UpdateDao::$GET_UPDATE_JOIN_AND_CONDITIONS);

        UpdateDao::$GET_UPDATE_JOIN_AND_CONDITIONS =
            str_replace('IN_UPDATE_TYPE', '('.implode(',', array_values($updateType)).')', UpdateDao::$GET_UPDATE_JOIN_AND_CONDITIONS);


        UpdateDao::$GET_UPDATE_JOIN_AND_CONDITIONS =
            str_replace('USER_UPDATE_CONDITION',$whereConditionOnUpdate, UpdateDao::$GET_UPDATE_JOIN_AND_CONDITIONS);


        $stmt = $this->adapter->createStatement(UpdateDao::$GET_UPDATE_JOIN_AND_CONDITIONS, array($user_id,
            $id, $user_id, $id, $limit, $user_id
        ));

        $stmt->prepare();
        $result = $stmt->execute();
        if ($result instanceof ResultInterface && $result->isQueryResult()) {
            $resultSet = new ResultSet ();
            $resultSet->initialize($result);
            $resultArray = array();
            foreach ($resultSet as $result) {
                $this->processUser($result);
                $this->processUpdate($result);
                array_push($resultArray, $result);
            }
            return $resultArray;
        }
    }

    public function getVoteUpdateData($id, $user_id, $updateType, $limit, $after){
        return $this->getUpdateDataWithWhereConditionGiven($id, $user_id, $updateType, $limit,
            $after, ' and user_update.poll_data is not null ');

    }

    public function getCommentData($id, $parentId, $noOfItems)
    {
        $stmt = $this->adapter->createStatement(UpdateDao::$GET_COMMENTS, array(
            $id,
            $parentId,
            $noOfItems
        ));
        $stmt->prepare();
        $result = $stmt->execute();
        if ($result instanceof ResultInterface && $result->isQueryResult()) {
            $resultSet = new ResultSet ();
            $resultSet->initialize($result);
	    $resultArray = array();
            foreach($resultSet as $comment){
		    $this->processUser($comment);
		    array_push($resultArray, $comment);
            }
            return $resultArray;
        }
    }

    private function getDefaultProfile(){
        return array(
            "tiny" => array("id" => 1, "type" => "tiny"),
            "feed" => array("id" => 2, "type" => "feed"),
            "profile" => array("id" => 3, "type" => "profile"),
        );
    }

    /**
     * @param $update
     */
    private function processUser($update)
    {
        $updatorPhoto = property_exists($update, 'photoset') ? $update->photoset : null;
        $commentorPhoto = property_exists($update, 'c_photoset') ? $update->c_photoset : null;

        $reader = new Xml ();
        if ($updatorPhoto != null) {
            $data = $reader->fromString($updatorPhoto);
            $update ['u_image_id'] = $data ['feed'] ['id'];
        } else {
            $update ['u_image_id'] = 2;
        }
        if ($commentorPhoto != null) {
            $data = $reader->fromString($commentorPhoto);
//		echo $data['feed'] ['id'];
            $update ['c_image_id'] = $data ['tiny'] ['id'];
	//	echo $commentorPhoto;
        } else {
            $update ['c_image_id'] = 1;
        }
    }


    private function processUpdate($update)
    {

        $pollData = $update->poll_data;
         $reader = new Xml ();
        if ($pollData != null) {
            $poll = Json::decode($pollData,  Json::TYPE_ARRAY);
            $update ['poll_data'] = $poll;
        }
    }




    /**
     * @param $comment
     * @param $commentor
     * @param $parentId
     * @param $parentType
     */
    public function saveCommentData($comment, $commenter, $parentId, $parentType)
    {
        $stmt = $this->adapter->createStatement(UpdateDao::$INSERT_COMMENT, array(
            $comment,
            $commenter,
            $parentId,
            $parentType
        ));
        $stmt = $stmt->prepare();
        $stmt->execute();
        $stmt = null;
    }

    /**
     * @param $id
     * @return null|void
     */
    public function getUpdateDetail($id)
    {
        $stmt = $this->adapter->createStatement(UpdateDao::$GET_UPDATE, array(
            $id));
        $stmt->prepare();
        $result = $stmt->execute();
        if ($result instanceof ResultInterface && $result->isQueryResult()) {
            $resultSet = new ResultSet ();
            $resultSet->initialize($result);
            $resultArray = array();
            foreach ($resultSet as $result) {
                $this->processUser($result);
                array_push($resultArray, $result);
            }
            return $resultArray;
            return null;
        }
    }
}

?>



